﻿using System;
using System.Collections.Generic;
using System.IO;
using NPOI.SS.UserModel;
using NPOI.HSSF.UserModel;
using NPOI.XSSF.UserModel;
using NPOI.HSSF.Util;
using NPOI.SS.Util;

namespace VATReader
{
    class Program
    {
        private static IWorkbook workBook = null;
        private static FileStream fs = null;
        private static string outPutPath = string.Empty;
        private static string companyName = "德尔福派克电气系统有限公司";
        private static string companyName1 = "潍柴";
        private static string date;
        private static string express = "SF";
        static int Main(string[] args)
        {
            int result = 0;
            try
            {
                Console.WriteLine("===============Aladdin Tool================");
                Console.WriteLine("参数:     设置表格输入和输出目录           ");
                Console.WriteLine("===========================================");
                if (args.Length == 0)
                {
                    Console.WriteLine("Error:请填写路径参数");
                    Console.ReadKey();
                    result = 1;
                }
                else if (args.Length > 1)
                {
                    DirectoryInfo dirInfo = new DirectoryInfo(args[0]);
                    outPutPath = args[1];
                    date = args[2];
                    if (!string.IsNullOrEmpty(args[3]))
                        express = args[3];
                    if (dirInfo == null)
                    {
                        Console.WriteLine("Error:路径有误");
                        Console.ReadKey();
                        result = 1;
                    }
                    else
                    {
                        GetExcels(dirInfo);
                        Console.ReadKey();
                        result = 0;
                    }
                }
                else
                {
                    Console.WriteLine("Error:参数只支持一个工程路径");
                    Console.ReadKey();
                    result = 1;
                }
            }
            catch (Exception ex)
            {
                Console.ForegroundColor = ((Console.ForegroundColor == ConsoleColor.Red) ? ConsoleColor.Green : ConsoleColor.Red);
                Console.WriteLine(ex.ToString());
                Console.ResetColor();
                result = 1;
            }
            return result;
        }

        static void GetExcels(DirectoryInfo folder)
        {
            try
            {
                var folders = folder.GetDirectories();
                for (int i = 0; i < folders.Length; i++)
                {
                    GetExcels(folders[i]);
                }

                var excelFiles = folder.GetFiles("*.xlsx");
                for (int i = 0; i < excelFiles.Length; i++)
                {
                    string fileName = folder.FullName + "\\" + excelFiles[i].Name;
                    ReadOneExcel(fileName);
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
        }

        static void ReadOneExcel(string fileName)
        {
            Console.WriteLine("正在导出，请耐心等待！\n");
            using (fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
            {
                workBook = new XSSFWorkbook(fs);
                XSSFSheet sheet = (XSSFSheet)workBook.GetSheetAt(0);

                //根据Type是EMS类型读取出对应的数据
                Dictionary<string, List<VATModel>> data = new Dictionary<string, List<VATModel>>();

                for (int i = 0; i <= sheet.LastRowNum; i++)
                {
                    VATModel model = new VATModel();
                    XSSFRow row = (XSSFRow)sheet.GetRow(i);
                    XSSFCell cell = (XSSFCell)row.GetCell(4);
                    if (cell.ToString().Equals(express))
                    {
                        model.InvoiceNum = row.GetCell(0).ToString();
                        model.InvoiceMoney = row.GetCell(1).ToString();
                        model.Company = row.GetCell(2).ToString();
                        model.Data = row.GetCell(3).ToString();
                        model.Type = row.GetCell(4).ToString();
                        if (model.Company.Contains(companyName))
                        {
                            List<VATModel> tempModels;
                            if (data.TryGetValue(companyName, out tempModels))
                            {
                                tempModels.Add(model);
                            }
                            else
                            {
                                tempModels = new List<VATModel>();
                                tempModels.Add(model);
                                data.Add(companyName, tempModels);
                            }
                        }
                        else if (model.Company.Contains(companyName1))
                        {
                            List<VATModel> tempModels;
                            if (data.TryGetValue(companyName1, out tempModels))
                            {
                                tempModels.Add(model);
                            }
                            else
                            {
                                tempModels = new List<VATModel>();
                                tempModels.Add(model);
                                data.Add(companyName1, tempModels);
                            }
                        }
                        else
                        {
                            List<VATModel> tempModels;
                            if (data.TryGetValue(model.Company, out tempModels))
                            {
                                tempModels.Add(model);
                            }
                            else
                            {
                                tempModels = new List<VATModel>();
                                tempModels.Add(model);
                                data.Add(model.Company, tempModels);
                            }
                        }
                    }
                }

                if (data.Count > 0)
                {
                    foreach (var pairValue in data)
                    {
                        if (pairValue.Value == null || pairValue.Value.Count <= 0)
                            continue;
                        if (pairValue.Key.Equals(companyName1))
                            WriteToExcel(Path.Combine(outPutPath, string.Format("{0}总公司.xlsx", companyName1)), pairValue.Key, pairValue.Value);
                        else
                            WriteToExcel(Path.Combine(outPutPath, pairValue.Key + ".xlsx"), pairValue.Key, pairValue.Value);
                    }
                    Console.WriteLine("导出成功!");
                }
                else
                {
                    Console.WriteLine("没有数据");
                }
            }
        }

        public static void WriteToExcel(string filePath, string name, List<VATModel> data)
        {
            //创建工作薄  
            IWorkbook wb;
            string extension = System.IO.Path.GetExtension(filePath);
            //根据指定的文件格式创建对应的类
            if (extension.Equals(".xls"))
            {
                wb = new HSSFWorkbook();
            }
            else
            {
                wb = new XSSFWorkbook();
            }

            //居中加粗
            ICellStyle titleStyle = wb.CreateCellStyle();
            titleStyle.Alignment = HorizontalAlignment.Center;
            titleStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font1 = wb.CreateFont();//字体
            font1.FontName = "宋体";
            font1.FontHeightInPoints = (short)16;
            font1.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
            titleStyle.SetFont(font1);

            //一般字体宋体11
            ICellStyle normalStyle = wb.CreateCellStyle();
            normalStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font2 = wb.CreateFont();
            font2.FontName = "宋体";
            font2.FontHeightInPoints = (short)11;
            normalStyle.SetFont(font2);

            //Arial 11 10
            ICellStyle arialStyle = wb.CreateCellStyle();
            arialStyle.Alignment = HorizontalAlignment.Center;
            arialStyle.VerticalAlignment = VerticalAlignment.Center;
            IFont font3 = wb.CreateFont();
            font3.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
            font3.FontName = "Arial";
            font3.FontHeightInPoints = (short)11;
            arialStyle.SetFont(font3);

            //Arial 12 EMS
            ICellStyle arialStyle1 = wb.CreateCellStyle();
            arialStyle1.Alignment = HorizontalAlignment.Left;
            arialStyle1.VerticalAlignment = VerticalAlignment.Center;
            IFont font4 = wb.CreateFont();
            font4.FontName = "Arial";
            font4.FontHeightInPoints = (short)12;
            arialStyle1.SetFont(font4);

            //正文 序号
            ICellStyle dengXianStyle1 = wb.CreateCellStyle();
            dengXianStyle1.Alignment = HorizontalAlignment.Center;
            dengXianStyle1.VerticalAlignment = VerticalAlignment.Center;
            IFont font5 = wb.CreateFont();
            font5.FontName = "DengXian";
            font5.FontHeightInPoints = (short)12;
            dengXianStyle1.SetFont(font5);
            dengXianStyle1.BorderBottom = BorderStyle.Thin;
            dengXianStyle1.BorderLeft = BorderStyle.Thick;
            dengXianStyle1.BorderRight = BorderStyle.Thin;
            dengXianStyle1.BorderTop = BorderStyle.Thin;

            //正文居中
            ICellStyle dengXianStyle2 = wb.CreateCellStyle();
            dengXianStyle2.Alignment = HorizontalAlignment.Center;
            dengXianStyle2.VerticalAlignment = VerticalAlignment.Center;
            dengXianStyle2.SetFont(font5);
            dengXianStyle2.BorderBottom = BorderStyle.Thin;
            dengXianStyle2.BorderLeft = BorderStyle.Thin;
            dengXianStyle2.BorderRight = BorderStyle.Thin;
            dengXianStyle2.BorderTop = BorderStyle.Thin;

            //正文居右
            ICellStyle dengXianStyle3 = wb.CreateCellStyle();
            dengXianStyle3.Alignment = HorizontalAlignment.Right;
            dengXianStyle3.VerticalAlignment = VerticalAlignment.Center;
            dengXianStyle3.SetFont(font5);
            dengXianStyle3.BorderBottom = BorderStyle.Thin;
            dengXianStyle3.BorderLeft = BorderStyle.Thin;
            dengXianStyle3.BorderRight = BorderStyle.Thick;
            dengXianStyle3.BorderTop = BorderStyle.Thin;

            //Arial 10 加粗 Total 左下边框加粗
            ICellStyle arialStyle20 = wb.CreateCellStyle();
            arialStyle20.Alignment = HorizontalAlignment.Center;
            arialStyle20.VerticalAlignment = VerticalAlignment.Center;
            IFont font6 = wb.CreateFont();
            font6.FontName = "Arial";
            font6.FontHeightInPoints = (short)12;
            font6.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
            arialStyle20.SetFont(font6);
            arialStyle20.BorderBottom = BorderStyle.Thick;
            arialStyle20.BorderLeft = BorderStyle.Thick;
            arialStyle20.BorderRight = BorderStyle.Thin;
            arialStyle20.BorderTop = BorderStyle.Thin;

            //Arial 10 加粗 居右
            ICellStyle arialStyle22 = wb.CreateCellStyle();
            arialStyle22.Alignment = HorizontalAlignment.Right;
            arialStyle22.VerticalAlignment = VerticalAlignment.Center;
            arialStyle22.SetFont(font6);
            arialStyle22.BorderBottom = BorderStyle.Thick;
            arialStyle22.BorderLeft = BorderStyle.Thin;
            arialStyle22.BorderRight = BorderStyle.Thick;
            arialStyle22.BorderTop = BorderStyle.Thin;


            //Arial 10 加粗 Total 下边框加粗
            ICellStyle arialStyle21 = wb.CreateCellStyle();
            arialStyle21.Alignment = HorizontalAlignment.Center;
            arialStyle21.VerticalAlignment = VerticalAlignment.Center;
            arialStyle21.SetFont(font6);
            arialStyle21.BorderBottom = BorderStyle.Thick;
            arialStyle21.BorderLeft = BorderStyle.Thin;
            arialStyle21.BorderRight = BorderStyle.Thin;
            arialStyle21.BorderTop = BorderStyle.Thin;


            //宋体11 加粗 垂直居中  签收人：
            ICellStyle normalStyle1 = wb.CreateCellStyle();
            normalStyle1.VerticalAlignment = VerticalAlignment.Center;
            IFont font7 = wb.CreateFont();
            font7.FontName = "宋体";
            font7.FontHeightInPoints = (short)11;
            font7.Boldweight = (short)FontBoldWeight.Bold;//字体加粗样式
            normalStyle1.SetFont(font7);

            //宋体11 加粗 居中  左上方边框加粗
            ICellStyle normalStyle00 = wb.CreateCellStyle();
            normalStyle00.VerticalAlignment = VerticalAlignment.Center;
            normalStyle00.Alignment = HorizontalAlignment.Center;
            normalStyle00.SetFont(font7);
            normalStyle00.BorderBottom = BorderStyle.Thin;
            normalStyle00.BorderLeft = BorderStyle.Thick;
            normalStyle00.BorderRight = BorderStyle.Thin;
            normalStyle00.BorderTop = BorderStyle.Thick;

            //宋体11 加粗 居中  上方边框加粗
            ICellStyle normalStyle01 = wb.CreateCellStyle();
            normalStyle01.VerticalAlignment = VerticalAlignment.Center;
            normalStyle01.Alignment = HorizontalAlignment.Center;
            normalStyle01.SetFont(font7);
            normalStyle01.BorderBottom = BorderStyle.Thin;
            normalStyle01.BorderLeft = BorderStyle.Thin;
            normalStyle01.BorderRight = BorderStyle.Thin;
            normalStyle01.BorderTop = BorderStyle.Thick;

            //宋体11 加粗 居中  右上方边框加粗
            ICellStyle normalStyle02 = wb.CreateCellStyle();
            normalStyle02.VerticalAlignment = VerticalAlignment.Center;
            normalStyle02.Alignment = HorizontalAlignment.Center;
            normalStyle02.SetFont(font7);
            normalStyle02.BorderBottom = BorderStyle.Thin;
            normalStyle02.BorderLeft = BorderStyle.Thin;
            normalStyle02.BorderRight = BorderStyle.Thick;
            normalStyle02.BorderTop = BorderStyle.Thick;


            //Arial 8 下面通用的说明
            ICellStyle arialStyle4 = wb.CreateCellStyle();
            arialStyle4.VerticalAlignment = VerticalAlignment.Center;
            IFont font8 = wb.CreateFont();
            font8.FontName = "Arial";
            font8.FontHeightInPoints = (short)8;
            arialStyle4.SetFont(font8);

            //创建一个表单
            ISheet sheet = wb.CreateSheet("Sheet0");

            //测试数据
            int baseRowCount = 27;
            int rowCount = baseRowCount + data.Count;
            int columnCount = 6;

            object[,] excelData = {
                                      {"发票收执","","","","",""},
                                      {"","","","","",""},
                                      {"现将以下增值税发票","","{}","份寄给您，具体内容如下：","",""},
                                      {"","","","", "",""},
                                      {"日期:","{}","","","",""},
                                      {"客户名称：","{}","","","",""},
                                      {"","","","","",""},
                                      {"序号","开票日期","发票号码","","含税金额",""},
                                      {"Total:","","","","{}",""},
                                      {"","","","","",""},
                                      {"","","","","",""},
                                      {"签收人:","","","","",""},
                                      {"签收日期:","","","","",""},
                                      {"","","由于国家对增值税发票的使用有了新的规定，在此我们特别提醒您：","","",""},
                                      {"如发票信息有误，","","1、请于收到发票时，尽快入账。","","",""},
                                      {"请及时联系销售","","根据新的规定，增值税专用发票必须在360天内入账，逾期则不能抵扣。","","",""},
                                      {"","","请注意：对于逾期的增值税专用发票，按新规定不能退票重开，","","",""},
                                      {"","","届时税金损失由贵司承担。","","",""},
                                      {"","","2、如果您发现我们的发票有错误，请务必于开票日后60天内将发票退回。","","",""},
                                      {"","","超过此时限，我公司没有足够时间办理退票重开手续，由此导致","","",""},
                                      {"谢谢您对我们工作的配合！","","的税款无法抵扣，损失将由贵司承担。","","",""},
                                      {"顺祝商祺！","","并请附上以下资料，以便我们办理发票重开手续。","","",""},
                                      {"","","⑴开票信息错误的，请指出错误信息，并出示正确信息，加盖公章后将","","",""},
                                      {"","","  原件及发票联、抵扣联寄回我公司；","","",""},
                                      {"","","⑵如果您是因为密码有误无法通过认证，则请复印出“无法认证”的相","","",""},
                                      {"","","  关凭证，写明拒收理由加盖公章后将原件及发票联、抵扣联寄回我司。","","",""},
                                      {"","","地址：江苏省无锡市新华路17号  博世汽车系统(无锡)有限公司","","",""},
                                  };

            IRow row;
            ICell cell;

            bool addRow = false;
            if (name.Contains(companyName1) || name.Contains(companyName))
            {
                addRow = true;
            }

            List<string> names = new List<string>();
            if (addRow)
            {
                foreach (var d in data)
                {
                    if (!names.Contains(d.Company))
                        names.Add(d.Company);
                }
            }

            for (int i = 0; i < 8 + names.Count; i++)
            {
                row = sheet.CreateRow(i);
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);
                    if (i == 0 && j == 0) //第一行
                        cell.CellStyle = titleStyle;
                    else if ((i == 2 && (j == 0 || j == 3)) || (i == 5 && (j == 0 || j == 1)))
                        cell.CellStyle = normalStyle;
                    else if (i == 2 && j == 2)
                        cell.CellStyle = arialStyle;
                    else if ((i == 4 && (j == 0 || j == 1)))
                        cell.CellStyle = arialStyle1;
                    if (names.Count == 0)
                    {
                        if (i == 7)
                        {
                            if (j == 0)
                                cell.CellStyle = normalStyle00;
                            else if (j == 5)
                                cell.CellStyle = normalStyle02;
                            else if (j > 0 || j < 5)
                                cell.CellStyle = normalStyle01;
                        }
                    }
                    else
                    {
                        if (i == 7 + names.Count)
                        {
                            if (j == 0)
                                cell.CellStyle = normalStyle00;
                            else if (j == 5)
                                cell.CellStyle = normalStyle02;
                            else if (j > 0 || j < 5)
                                cell.CellStyle = normalStyle01;
                        }
                    }
                    if (names.Count == 0)
                    {
                        if (i == 5 && j == 1)
                        {
                            SetCellValue(cell, name);
                        }
                        else if (i == 2 && j == 2)
                        {
                            SetCellValue(cell, data.Count);
                        }
                        else if (i == 4 && j == 1)
                        {
                            SetCellValue(cell, date);
                        }
                        else
                        {
                            SetCellValue(cell, excelData[i, j]);
                        }
                    }
                    else
                    {
                        if (i == 2 && j == 2)
                        {
                            SetCellValue(cell, data.Count);
                        }
                        else if (i == 4 && j == 1)
                        {
                            SetCellValue(cell, date);
                        }
                        else if ((i >= 5 && i < 5 + names.Count) && j == 1)
                        {
                            SetCellValue(cell, names[i - 5]);
                        }
                        else if (i <= 5)
                        {
                            SetCellValue(cell, excelData[i, j]);
                        }
                        else if (i > 5 + names.Count)
                        {
                            SetCellValue(cell, excelData[i - names.Count, j]);
                        }
                    }
                    sheet.AutoSizeColumn(j);
                }
            }

            int num = 1;
            int index = 8 + data.Count + names.Count;
            for (int i = 8 + names.Count; i < index; i++)
            {
                row = sheet.CreateRow(i);
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);
                    if (j == 0)
                    {
                        SetCellValue(cell, num);
                        cell.CellStyle = dengXianStyle1;
                    }
                    else if (j == 1)
                    {
                        SetCellValue(cell, data[num - 1].Data);
                        cell.CellStyle = dengXianStyle2;
                    }
                    else if (j == 2)
                    {
                        SetCellValue(cell, data[num - 1].InvoiceNum);
                        cell.CellStyle = dengXianStyle2;
                    }
                    else if (j == 3 || j == 4)
                    {
                        cell.CellStyle = dengXianStyle2;
                        if (j == 4)
                        {
                            SetCellValue(cell, data[num - 1].InvoiceMoney);
                        }
                    }
                    else if (j == 5)
                    {
                        cell.CellStyle = dengXianStyle3;
                    }
                }
                num++;
            }

            int end = baseRowCount + data.Count + names.Count;
            for (int i = index; i < end; i++)
            {
                row = sheet.CreateRow(i);
                for (int j = 0; j < columnCount; j++)
                {
                    cell = row.CreateCell(j);
                    if (i == index)
                    {
                        if (j == 0)
                            cell.CellStyle = arialStyle20;
                        else if (j == 5)
                            cell.CellStyle = arialStyle22;
                        else if (j > 0 || j < 5)
                            cell.CellStyle = arialStyle21;
                    }
                    else if (i == index + 3 || i == index + 4)
                        cell.CellStyle = normalStyle1;
                    else if (i > index + 4)
                        cell.CellStyle = arialStyle4;
                    if (i == index && j == 4)
                    {
                        double totalNum = 0;
                        //计算所有的金额
                        foreach (var m in data)
                        {
                            totalNum += Convert.ToDouble(m.InvoiceMoney);
                        }
                        SetCellValue(cell, totalNum);
                    }
                    else
                    {
                        if (names.Count == 0)
                            SetCellValue(cell, excelData[i - data.Count, j]);
                        else
                            SetCellValue(cell, excelData[i - data.Count - names.Count, j]);
                    }

                    sheet.AutoSizeColumn(j);
                }
            }

            //设置列宽
            int[] columnWidth = { 16, 22, 13, 3, 10, 14 };
            for (int i = 0; i < columnWidth.Length; i++)
            {
                //设置列宽度，256*字符数，因为单位是1/256个字符
                sheet.SetColumnWidth(i, 256 * columnWidth[i]);
            }

            //合并标题
            CellRangeAddress region = new CellRangeAddress(0, 0, 0, 5);
            sheet.AddMergedRegion(region);
            //合并表格23 和45列合并
            for (int i = 7 + names.Count; i < 9 + names.Count + data.Count; i++)
            {
                region = new CellRangeAddress(i, i, 2, 3);
                sheet.AddMergedRegion(region);
                region = new CellRangeAddress(i, i, 4, 5);
                sheet.AddMergedRegion(region);
            }
            try
            {
                FileStream fs = File.OpenWrite(filePath);
                wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。  
                fs.Close();
            }
            catch (Exception e)
            {
                Console.WriteLine(e.Message);
                Console.Read();
            }
        }

        static void SetCellValue(ICell cell, object obj)
        {
            if (obj.GetType() == typeof(int))
            {
                cell.SetCellValue((int)obj);
            }
            else if (obj.GetType() == typeof(double))
            {
                cell.SetCellValue((double)obj);
            }
            else if (obj.GetType() == typeof(IRichTextString))
            {
                cell.SetCellValue((IRichTextString)obj);
            }
            else if (obj.GetType() == typeof(string))
            {
                cell.SetCellValue(obj.ToString());
            }
            else if (obj.GetType() == typeof(DateTime))
            {
                cell.SetCellValue((DateTime)obj);
            }
            else if (obj.GetType() == typeof(bool))
            {
                cell.SetCellValue((bool)obj);
            }
            else
            {
                cell.SetCellValue(obj.ToString());
            }
        }
    }
}
